package com.dao;

import com.entity.User;
import com.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserInfo implements UserDao{

    //根据姓名、年龄、性别查询
    @Override
    public User selectUser(String name,Integer age,String sex) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "select * from user_table where u_name=? and u_age=? and u_sex=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name );
        ps.setInt(2,age );
        ps.setString(3, sex);

        ResultSet rs = ps.executeQuery();

        User user = null;
        while (rs.next()){
            Integer u_id = rs.getInt("u_id");
            String u_name = rs.getString("u_name");
            String u_pwd = rs.getString("u_pwd");
            Integer u_age = rs.getInt("u_age");
            String u_sex = rs.getString("u_sex");
            Date addDate = rs.getTime("u_add");
            Date update = rs.getTimestamp("u_update");
            user = new User(u_id,u_name,u_pwd,u_age,u_sex,addDate,update);
        }
        DBUtil.close(rs,ps,conn);
        return user;
    }

    //id查找某个用户
    @Override
    public User selectOneUser(Integer u_id) throws SQLException {
//        User user = null;
        Connection conn = DBUtil.getConn();
        String sql = "select * from  user_table where u_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,u_id );
        ResultSet rs = ps.executeQuery();
//        while (set.next()){
//            Integer u_id = set.getInt("u_id");
            String u_name = rs.getString("u_name");
            String u_pwd = rs.getString("u_pwd");
            Integer u_age = rs.getInt("u_age");
            String u_sex = rs.getString("u_sex");
            Date addDate = rs.getTime("u_add");
            Date update = rs.getTimestamp("u_update");
        User user = new User(u_id, u_name, u_pwd, u_age,u_sex , addDate,update );
//        }
        DBUtil.close(rs,ps,conn);
        return user;
    }

    //修改员工信息
    @Override
    public boolean update(Integer id,String userName,String userPwd,Integer age,String userSex) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "update user_table set u_name = ? ,u_pwd=?," +
         "u_age=?,u_sex=? where u_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
//        ResultSet rs = ps.executeQuery();
        ps.setString(1,userName );
        ps.setString(2,userPwd );
        ps.setInt(3,age );
        ps.setString(4,userSex );
        ps.setInt(5,id );
        int result = ps.executeUpdate();
        DBUtil.close(null,ps,conn);
        return result > 0;
    }
    //辞退员工
    @Override
    public boolean deleteUser(Integer u_id) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "delete from  user_table where u_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
//        ResultSet rs = ps.executeQuery();
        ps.setInt(1,u_id);
        int result = ps.executeUpdate();
        DBUtil.close(null,ps,conn);
        return result > 0;
    }
    //添加员工
    @Override
    public boolean insertUser(String name,String passPwd,Integer age,String sex) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "insert into user_table(u_name, u_pwd, u_age,u_sex)values(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
//        ResultSet rs = ps.executeQuery();
        ps.setString(1,name);
        ps.setString(2,passPwd);
        ps.setInt(3,age);
        ps.setString(4,sex);
        int result = ps.executeUpdate();
        DBUtil.close(null,ps,conn);
        return result > 0;
    }


    //查询所有用户
    @Override
    public List<User> userAll() throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "select * from user_table";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()){
            Integer u_id = rs.getInt("u_id");
            String u_name = rs.getString("u_name");
            String u_pwd = rs.getString("u_pwd");
            Integer u_age = rs.getInt("u_age");
            String u_sex = rs.getString("u_sex");
            Date addDate = rs.getTime("u_add");
            Date update = rs.getTimestamp("u_update");
            userList.add(new User(u_id,u_name,u_pwd,u_age,u_sex,addDate,update));
        }
        DBUtil.close(rs,ps,conn);
        return userList;
    }
    //查询输入参数的对应的用户
    @Override
    public User login(String name,String password) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "select * from user_table where u_name=? and u_pwd=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setString(2,password);

        ResultSet rs = ps.executeQuery();
        User user = null;
        while (rs.next()){
            Integer u_id = rs.getInt("u_id");
            String u_name = rs.getString("u_name");
            String u_pwd = rs.getString("u_pwd");
            Integer u_age = rs.getInt("u_age");
            String u_sex = rs.getString("u_sex");
            Date addDate = rs.getTime("u_add");
            Date update = rs.getTimestamp("u_update");
            user = new User(u_id,u_name,u_pwd,u_age,u_sex,addDate,update);
        }
        DBUtil.close(rs,ps,conn);
        return user;
    }

}
